import os
import time
from decimal import Decimal
from testcases.financialReconciliation.AliExpress_kingdee.test_collectCompareAliExpressReport import \
    create_sqlserver_database_conn
from testcases.financialReconciliation.AliExpress_kingdee.test_collectCompareAliExpressReport import exec_query_sql_more
import mysql.connector
from common.setting import ensure_path_sep
from utils.logging_tool.log_control import LogHandler
current_path = os.path.abspath(__file__)
current_dir = os.path.dirname(current_path)
path_list = current_path.split("\\")
dis_path = "\\".join(path_list[-2:-1])



def remove_file(info_path):
    if os.path.exists(info_path):
        os.remove(info_path)
    else:
        print(f"{info_path} 不存在")


info_path = ensure_path_sep(f"\\logs\\info-{dis_path}{time.time()}")
error_path = ensure_path_sep(f"\\logs\\\error-{dis_path}{time.time()}.log")
warning_path = ensure_path_sep(f"\\logs\\warning-{dis_path}{time.time()}.log")
remove_file(info_path)
remove_file(error_path)
remove_file(warning_path)
INFO = LogHandler(info_path, level='info')
ERROR = LogHandler(error_path, level='error')
WARNING = LogHandler(warning_path, level='warning')

class DataComparison:
    def __init__(self,database):
        """
        database为字典类型，支持同库数据对比、sqlserver和mysql库数据对比
        sqlserver的key为：库别名，value值为sqlserver|ip|端口|用户名|密码|数据库名
        mysql的key为：库别名，value值为mysql|ip|用户名|密码|数据库名
        示列：{'erp库':'sqlserver|ip|端口|用户名|密码|数据库名','金蝶库':'mysql|ip|用户名|密码|数据库名'}
        """
        self.sqlserver_conn={}
        self.mysql_conn ={}
        for key,values in database.items():
            conn_data = values.split('|')
            if conn_data[0]=='sqlserver':
                erp_conn = create_sqlserver_database_conn(conn_data[1], int(conn_data[2]), conn_data[3],
                                                               conn_data[4], conn_data[5])
                self.sqlserver_conn[key]=erp_conn
            elif conn_data[0]=='mysql':
                kingdee_conn = mysql.connector.connect(
                    host=conn_data[1],
                    user=conn_data[3],
                    password=conn_data[4],
                    database=conn_data[5])
                self.mysql_conn[key]=kingdee_conn
        # self.erp_conn = create_sqlserver_database_conn('erpdb-readonly.zhcxkj.com', 1433, 'irobotboxuser','HskwPanda047RAHRAxqktlref', 'irobotbox')
        #
        # self.kingdee_conn = mysql.connector.connect(
        #     host="139.159.227.93",
        #     user="reader",
        #     password="5vv1YDXCR3W8ngad#",
        #     database="irobotbox_finance")

    def data_organization(self,database,sql,keys,values):
        """
        该函数用于将数据处理成字典类型，方便查找
        :param database: 数据库名称
        :param sql: string类型，要执行的sql
        :param keys: 新字典的key，从sql查询字段中取，有多个，传元组
        :param values: 新字典的values，从sql查询字段中取，有多个，传元组
        """
        if self.sqlserver_conn.get(database):
            original_data = exec_query_sql_more(sql,self.sqlserver_conn[database], None)
        elif self.mysql_conn.get(database):
            original_data = exec_query_sql_more(sql, self.mysql_conn[database], None)
        if len(original_data) > 0:
            # original_data = [dict(t) for t in {tuple(sorted(d.items())) for d in original_data}]
            if type(keys)==str and type(values)==str:
                original_data_dict = dict(map(lambda x: (x[keys],x[values]),original_data))
            elif type(keys)!=str and type(values)!=str:
                original_data_dict = dict(map(lambda x: (tuple((x[key] for key in keys)),tuple((x[key] for key in values))), original_data))
            elif type(keys)==str and type(values)!=str:
                original_data_dict = dict(map(lambda x: (x[keys], tuple((x[key] for key in values))), original_data))
            elif type(keys)!=str and type(values)==str:
                original_data_dict = dict(map(lambda x: (tuple((x[key] for key in keys)),x[values]), original_data))
        else:
            return {}
        return original_data_dict

    def data_format(self, num):
        return round(Decimal(num),2)

    def data_compare(self,expect:dict,actual:dict,field,printexcel=0):
        loss_actual= set(expect.keys()) - set(actual.keys())
        more_actual = set(actual.keys()) - set(expect.keys())
        if printexcel == 1:
            text='系统订单'+','+','.join(field)
            with open('one_diff.csv', 'a', encoding='utf-8') as f:
                f.write(text+'\n')
        if type(field)!=str:
            try:
                loss_actual=set(filter(lambda x:sum(expect[x])!=0,loss_actual))
            except Exception as e:
                print("Exception:", e)
            more_actual=set(filter(lambda x:sum(actual[x])!=0,more_actual))
        else:
            loss_actual = set(filter(lambda x: expect[x] != 0, loss_actual))
            more_actual = set(filter(lambda x: actual[x] != 0, more_actual))
        for i in loss_actual:
            ERROR.logger.error(f"{i}预期有，实际没有 实际缺的:{expect[i]} ")
            if printexcel==1:
                tmp=tuple(map(lambda x:str(x),expect[i]))
                text=','.join(tmp)
                newtext=f'{i}预期有，实际没有'+','+text
                with open('one_diff.csv', 'a', encoding='utf-8') as f:
                    f.write(newtext+'\n')
        for i in more_actual:
            ERROR.logger.error(f"{i}预期没有，实际有 实际多的:{actual[i]} ")
            if printexcel==1:
                tmp = tuple(map(lambda x: str(x), actual[i]))
                text=','.join(tmp)
                newtext=f'{i}预期没有，实际有'+','+text
                with open('one_diff.csv', 'a', encoding='utf-8') as f:
                    f.write(newtext+'\n')
        if type(field) == str:
            for key, value in expect.items():
                if actual.get(key)!=None and self.data_format(actual.get(key))!=self.data_format(value):
                    ERROR.logger.error(f"{key} {field}不相等：预期：{self.data_format(value)} 实际：{self.data_format(actual.get(key))} 相差：{self.data_format(value)-self.data_format(actual.get(key))}")
        else:
            for key, value in expect.items():
                if actual.get(key) != None and actual.get(key) != value:
                    for i in range(len(value)):
                        if self.data_format(value[i])!=self.data_format(actual.get(key)[i]):
                            ERROR.logger.error(
                                f"{key} {field[i]}不相等：预期：{self.data_format(value[i])} 实际：{self.data_format(actual.get(key)[i])} 相差：{self.data_format(value[i])-self.data_format(actual.get(key)[i])}")
                            with open('both_diff.csv', 'a', encoding='utf-8') as fr:
                                newstr=f"{key} {field[i]}不相等"+','+f'预期：{self.data_format(value[i])}'+','+f'实际：{self.data_format(actual.get(key)[i])}'+','+f'相差：{self.data_format(value[i])-self.data_format(actual.get(key)[i])}'
                                fr.write(newstr+'\n')
    def data_handle(self,kingdee_data,erp_data,isprint,issave=0,Fields=None,operate=None):
        """
        该函数用于处理数据和对比数据，处理数据时默认前面的数据-后面的数据，对比数据时默认以前面的数据为准
        :param kingdee_data: dict类型
        :param erp_data: dict类型
        :param isprint: 1或者0，是否打印两边中一边有，一边无的数据，传1则打印，传0则不打印
        :param issave: 1或者0，是否保存两边中只有一边的数据，比如：kingdee_data有key，但erp_data没有key，1表示保留数据，0表示抛弃数据
        :param Fields: 需要处理的value值，是元组传对应的索引位置，否则不用传
        :param operate: 操作符号，默认为None，枚举:-
        :return: 处理好的数据，为字典类型
        """
        new_dict={}
        loss_inerp=set(kingdee_data.keys()) - set(erp_data.keys())
        loss_inkingdee=set(erp_data.keys()) - set(kingdee_data.keys())
        if issave==1:
            for i in loss_inerp:
                new_dict[i]=kingdee_data[i]
            for i in loss_inkingdee:
                new_dict[i]=erp_data[i]
        if isprint==1:
            for i in loss_inerp:
                ERROR.logger.error(f"{i}左边有，右边没有 左边:{kingdee_data[i]} ")
            for i in loss_inkingdee:
                ERROR.logger.error(f"{i}左边没有，右边有 右边:{erp_data[i]} ")
        if operate!=None:
            for key, value in kingdee_data.items():
                if erp_data.get(key)!= None and  operate=='-':
                    if type(value)!=tuple:
                        new_dict[key]=kingdee_data[key]-erp_data[key]
                    else:
                        new_dict[key]=tuple((kingdee_data[key][i]-erp_data[key][i] for i in Fields))
            return new_dict
        else:
            for key, value in kingdee_data.items():
                if erp_data.get(key) != None and value!=erp_data.get(key):
                    ERROR.logger.error(f"{key} value不相等 左边:{value} 右边:{erp_data.get(key)}")




#query_erp_custom_report = '''
#    declare @startdate datetime='2024-01-01 00:00:00';
# declare @enddate datetime='2024-01-31 23:59:59';
# --@CustomerID=1
# --新版
#
# SELECT '国际支付宝' AS '速卖通区域',MAX(a.BillDate) '结算时间',a.MerchantOrderNumber '平台订单号',a.OrderSourceID '店铺ID',a.OrderSourceName '店铺',a.Currency '币种',
# ISNULL((CASE WHEN a.BillType = '收款' AND a.Remarks LIKE '放款%' THEN '订单收入'
# 			 WHEN a.BillType = '分账' AND a.Remarks LIKE '平台佣金扣佣%' THEN '订单收入'
# 			 WHEN a.BillType = '扣款' AND a.Remarks LIKE '平台佣金扣佣%' THEN '订单收入'
# 			 WHEN a.BillType = '收款'
# 				 AND a.Remarks NOT LIKE '放款%'
# 				 AND a.Remarks NOT LIKE '赔付%'
# 				 AND a.Remarks NOT LIKE 'DDP关税%'
# 				 AND REPLACE(a.Remarks, ' ', '_') NOT LIKE '菜鸟代发_代发%'
# 				 AND a.Remarks NOT LIKE '违背发货%'
# 				 AND a.Remarks NOT LIKE '本地退买家%'
# 				 AND REPLACE(a.Remarks, ' ', '_') NOT LIKE '菜鸟物流费代扣_代扣%'
# 				 AND a.Remarks NOT LIKE '巴西%'
# 				 AND a.Remarks NOT LIKE '退还EPR费用%'
# 				 AND a.Remarks NOT LIKE '环保费%'
# 				 AND a.Remarks NOT LIKE 'AE_EU_ENVIRONMENT%'
# 				 AND a.Remarks NOT LIKE '佣金退回%'
# 				 AND a.Remarks NOT LIKE '平台佣金退回%'
# 				 AND a.Remarks NOT LIKE '联盟佣金退回%'
# 				 AND REPLACE(a.Remarks, ' ', '_') NOT LIKE '3PL_%'
# 				 AND REPLACE(a.Remarks, ' ', '_') NOT LIKE '4PL_%'
# 			     AND a.Remarks NOT LIKE '退款垫付%'
# 				 AND a.Remarks NOT LIKE '半托管%'
# 			 THEN '订单收入'
# 			 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE 'For_quality_or%' THEN '订单退款'
# 			 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_trade_order_refund%' THEN '订单退款'
# 			 WHEN a.BillType = '退款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_trade_order_refund%' THEN '订单退款'
# 			 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE 'trade_order%' THEN '订单退款'
# 			 WHEN a.BillType = '退款' AND REPLACE(a.Remarks, ' ', '_') LIKE 'trade_order%' THEN '订单退款'
# 			 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_For_quality_or_delivery_issue%' THEN '订单退款'
# 			 WHEN a.BillType = '退款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_For_quality_or_delivery_issue%' THEN '订单退款'
# 			 WHEN a.BillType = '收款' AND a.Remarks LIKE '佣金退回%' THEN '订单退款'
# 			 WHEN a.BillType = '扣款' AND a.Remarks LIKE '佣金退回%' THEN '订单退款'
# 			 WHEN a.BillType = '分账退款' AND a.Remarks LIKE '佣金退回%' THEN '订单退款'
# 			 WHEN a.BillType = '收款' AND a.Remarks LIKE '平台佣金退回%' THEN '订单退款'
# 			 WHEN a.BillType = '扣款' AND a.Remarks LIKE '平台佣金退回%' THEN '订单退款'
# 			 WHEN a.BillType = '分账退款' AND a.Remarks LIKE '平台佣金退回%' THEN '订单退款'
# 			 WHEN a.BillType = '退款'
# 				 AND a.Remarks NOT LIKE '退款%'
# 				 AND a.Remarks NOT LIKE 'DDP关税%'
# 				 AND a.Remarks NOT LIKE '违背发货%'
# 				 AND a.Remarks NOT LIKE '本地退买家%'
# 				 AND REPLACE(a.Remarks, ' ', '_') NOT LIKE '菜鸟物流费代扣_代扣%'
# 				 AND a.Remarks NOT LIKE '巴西%'
# 				 AND a.Remarks NOT LIKE '退还EPR费用%'
# 				 AND REPLACE(a.Remarks, ' ', '_') NOT LIKE 'trade_order%'
# 			 THEN '订单退款'
# 	  ELSE '非订单' END),'') AS '单据类型',
# SUM(CASE WHEN a.BillType = '收款' AND a.Remarks LIKE '放款%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE 'For_quality_or%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_trade_order_refund%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '退款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_trade_order_refund%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE 'trade_order%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '退款' AND REPLACE(a.Remarks, ' ', '_') LIKE 'trade_order%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_For_quality_or_delivery_issue%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '退款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_For_quality_or_delivery_issue%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '收款'
# 				 AND a.Remarks NOT LIKE '放款%'
# 				 AND a.Remarks NOT LIKE '赔付%'
# 				 AND a.Remarks NOT LIKE 'DDP关税%'
# 				 AND REPLACE(a.Remarks, ' ', '_') NOT LIKE '菜鸟代发_代发%'
# 				 AND a.Remarks NOT LIKE '违背发货%'
# 				 AND a.Remarks NOT LIKE '本地退买家%'
# 				 AND REPLACE(a.Remarks, ' ', '_') NOT LIKE '菜鸟物流费代扣_代扣%'
# 				 AND a.Remarks NOT LIKE '巴西%'
# 				 AND a.Remarks NOT LIKE '退还EPR费用%'
# 				 AND a.Remarks NOT LIKE '环保费%'
# 				 AND a.Remarks NOT LIKE 'AE_EU_ENVIRONMENT%'
# 				 AND a.Remarks NOT LIKE '佣金退回%'
# 				 AND a.Remarks NOT LIKE '平台佣金退回%'
# 				 AND a.Remarks NOT LIKE '联盟佣金退回%'
# 				 AND REPLACE(a.Remarks, ' ', '_') NOT LIKE '3PL_%'
# 				 AND REPLACE(a.Remarks, ' ', '_') NOT LIKE '4PL_%'
# 			     AND a.Remarks NOT LIKE '退款垫付%'
# 				 AND a.Remarks NOT LIKE '半托管%'
# 			 THEN ISNULL(a.Amount,0)
# 			 WHEN a.BillType = '退款'
# 				 AND a.Remarks NOT LIKE '退款%'
# 				 AND a.Remarks NOT LIKE 'DDP关税%'
# 				 AND a.Remarks NOT LIKE '违背发货%'
# 				 AND a.Remarks NOT LIKE '本地退买家%'
# 				 AND REPLACE(a.Remarks, ' ', '_') NOT LIKE '菜鸟物流费代扣_代扣%'
# 				 AND a.Remarks NOT LIKE '巴西%'
# 				 AND a.Remarks NOT LIKE '退还EPR费用%'
# 				 AND REPLACE(a.Remarks, ' ', '_') NOT LIKE 'trade_order%'
# 			 THEN ISNULL(a.Amount,0)
# ELSE 0 END) AS '销售收入',
# SUM(CASE WHEN a.BillType = '分账' AND a.Remarks LIKE '平台佣金扣佣%' THEN ISNULL(a.Amount,0)
# 	     WHEN a.BillType = '扣款' AND a.Remarks LIKE '平台佣金扣佣%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '收款' AND a.Remarks LIKE '佣金退回%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '佣金退回%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '分账退款' AND a.Remarks LIKE '佣金退回%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '收款' AND a.Remarks LIKE '平台佣金退回%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '平台佣金退回%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '分账退款' AND a.Remarks LIKE '平台佣金退回%' THEN ISNULL(a.Amount,0)
# ELSE 0 END) AS '佣金',
# SUM(CASE WHEN a.BillType = '收款' AND a.Remarks LIKE '赔付%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '收款' AND REPLACE(a.Remarks, ' ', '_') LIKE '菜鸟代发_代发%' AND REPLACE(a.Remarks , ' ', '_') NOT LIKE '菜鸟代发_代发_正向配送费%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE '菜鸟代发_代发%' AND REPLACE(a.Remarks , ' ', '_') NOT LIKE '菜鸟代发_代发_正向配送费%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE '菜鸟物流费代扣_代扣%' AND REPLACE(a.Remarks , ' ', '_') NOT LIKE '菜鸟物流费代扣_代扣_正向配送费%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '收款' AND a.Remarks LIKE 'DDP关税%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '分账' AND a.Remarks LIKE 'DDP关税%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND a.Remarks LIKE 'DDP关税%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '退款' AND a.Remarks LIKE 'DDP关税%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '收款' AND a.Remarks LIKE '退还EPR费用%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '退款' AND a.Remarks LIKE '退还EPR费用%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '收款' AND a.Remarks LIKE '环保费%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '环保费%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '中东地区满包邮活动服务费%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '垫资/欠费还款的解冻并扣款%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '买家退货运费%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND a.Remarks LIKE 'AE_EU_ENVIRONMENT%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '收款' AND a.Remarks LIKE 'AE_EU_ENVIRONMENT%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '限时达不达标处罚%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '垫资还款冻结%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND a.Remarks LIKE 'EPR费用%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '垫付' AND a.Remarks LIKE '退款垫付%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND a.Remarks LIKE 'POP_SELLER_DELIVER%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '收款' AND a.Remarks LIKE '违背发货%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '退款' AND a.Remarks LIKE '违背发货%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '违背发货%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '收款' AND a.Remarks LIKE '巴西%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '退款' AND a.Remarks LIKE '巴西%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '巴西%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '收款' AND a.Remarks LIKE '本地退买家%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '退款' AND a.Remarks LIKE '本地退买家%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '本地退买家%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '收款' AND REPLACE(a.Remarks, ' ', '_') LIKE '3PL_%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '收款' AND a.Remarks LIKE '退款垫付%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE '4PL_%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '收款' AND a.Remarks LIKE '半托管%' AND a.Remarks NOT LIKE '半托管发货物流费%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '半托管%' AND a.Remarks NOT LIKE '半托管发货物流费%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '分账' AND a.Remarks LIKE '半托管%' AND a.Remarks NOT LIKE '半托管发货物流费%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '收款'
# 		 AND a.Remarks NOT LIKE '放款%'
# 		 AND REPLACE(a.Remarks , ' ', '_') NOT LIKE '菜鸟代发_代发_正向配送费%'
# 		 AND a.Remarks NOT LIKE '佣金退回%'
# 		 AND a.Remarks NOT LIKE '平台佣金退回%'
# 		 AND a.Remarks NOT LIKE '联盟佣金退回%'
# 		 AND a.Remarks NOT LIKE '半托管发货物流费%'
# 		 THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款'
# 		 AND REPLACE(a.Remarks, ' ', '_') NOT LIKE 'For_quality_or%'
# 		 AND REPLACE(a.Remarks, ' ', '_') NOT LIKE '退款_trade_order_refund%'
# 		 AND REPLACE(a.Remarks, ' ', '_') NOT LIKE 'trade_order%'
# 		 AND REPLACE(a.Remarks, ' ', '_') NOT LIKE '退款_For_quality_or_delivery_issue%'
# 		 AND a.Remarks NOT LIKE '平台佣金扣佣%'
# 		 AND a.Remarks NOT LIKE '佣金退回%'
# 		 AND a.Remarks NOT LIKE '平台佣金退回%'
# 		 AND REPLACE(a.Remarks, ' ', '_') NOT LIKE '菜鸟代发_代发%'
# 		 AND REPLACE(a.Remarks, ' ', '_') NOT LIKE '菜鸟物流费代扣_代扣%'
# 		 AND a.Remarks NOT LIKE 'DDP关税%'
# 		 AND a.Remarks NOT LIKE '环保费%'
# 		 AND a.Remarks NOT LIKE '中东地区满包邮活动服务费%'
# 		 AND a.Remarks NOT LIKE '垫资/欠费还款的解冻并扣款%'
# 		 AND a.Remarks NOT LIKE '买家退货运费%'
# 		 AND a.Remarks NOT LIKE 'AE_EU_ENVIRONMENT%'
# 		 AND a.Remarks NOT LIKE '限时达不达标处罚%'
# 		 AND a.Remarks NOT LIKE '垫资还款冻结%'
# 		 AND a.Remarks NOT LIKE 'EPR费用%'
# 		 AND REPLACE(a.Remarks, ' ', '_') NOT LIKE 'POP_SELLER_DELIVER%'
# 		 AND a.Remarks NOT LIKE '违背发货%'
# 		 AND a.Remarks NOT LIKE '巴西%'
# 		 AND a.Remarks NOT LIKE '本地退买家%'
# 		 AND a.Remarks NOT LIKE '直通车充值%'
# 		 AND a.Remarks NOT LIKE 'AEP4P回款金充值%'
# 		 AND a.Remarks NOT LIKE '联盟佣金扣佣%'
# 		 AND a.Remarks NOT LIKE '联盟佣金退回%'
# 		 AND REPLACE(a.Remarks, ' ', '_') NOT LIKE '4PL_%'
# 		 AND a.Remarks NOT LIKE '半托管发货物流费%'
# 		 THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '分账'
# 		 AND a.Remarks NOT LIKE '平台佣金扣佣%'
# 		 AND a.Remarks NOT LIKE 'DDP关税%'
# 		 AND a.Remarks NOT LIKE '联盟佣金扣佣%'
# 		 AND a.Remarks NOT LIKE '半托管发货物流费%'
# 		 THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '分账退款'
# 		 AND a.Remarks NOT LIKE '佣金退回%'
# 		 AND a.Remarks NOT LIKE '平台佣金退回%'
# 		 AND a.Remarks NOT LIKE '联盟佣金退回%'
# 		 THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '服务费'
# 		 AND a.Remarks !=''
# 		 AND a.Remarks NOT LIKE '自动转账%'
# 		 THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '垫付'
# 		 AND a.Remarks NOT LIKE '退款垫付%'
# 		 THEN ISNULL(a.Amount,0)
# ELSE 0 END) AS '平台其他费用',
# SUM(CASE WHEN a.BillType = '收款' AND REPLACE(a.Remarks, ' ', '_') LIKE '菜鸟代发_代发_正向配送费%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE '菜鸟代发_代发_正向配送费%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE '菜鸟物流费代扣_代扣_正向配送费%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '收款' AND a.Remarks LIKE '半托管发货物流费%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '半托管发货物流费%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '分账' AND a.Remarks LIKE '半托管发货物流费%' THEN ISNULL(a.Amount,0)
# ELSE 0 END) AS '平台运费',
# SUM(CASE WHEN a.BillType = '扣款' AND a.Remarks LIKE '直通车充值%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND a.Remarks LIKE 'AEP4P回款金充值%' THEN ISNULL(a.Amount,0)
# ELSE 0 END) AS '充值广告费',
# SUM(CASE WHEN a.BillType = '分账' AND a.Remarks LIKE '联盟佣金扣佣%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '联盟佣金扣佣%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '收款' AND a.Remarks LIKE '联盟佣金退回%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '扣款' AND a.Remarks LIKE '联盟佣金退回%' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '分账退款' AND a.Remarks LIKE '联盟佣金退回%' THEN ISNULL(a.Amount,0)
# ELSE 0 END) AS '广告费',
# SUM(CASE WHEN a.BillType = '服务费' AND a.Remarks ='' THEN ISNULL(a.Amount,0)
# 		 WHEN a.BillType = '服务费' AND a.Remarks LIKE '自动转账%' THEN ISNULL(a.Amount,0)
# ELSE 0 END) AS '财务费用'
# FROM RB_Crawler_AliExpress_BillHistory a WITH(NOLOCK)
# WHERE (a.BillType !='转账' AND a.BillType !='兑换')
# AND a.BillDate >= @startdate
# AND a.BillDate <= @enddate
# --AND a.BillDate >= '2023-12-01'
# --AND a.BillDate < '2024-01-01'
# --AND a.MerchantOrderNumber='3029716833756565'
# --AND a.OrderSourceID = 6160
# --AND a.Amount = -5.77
# -- AND {{ a.OrderSourceID IN (@OrderSourceID) }}
# GROUP BY a.MerchantOrderNumber,a.OrderSourceID,a.OrderSourceName,a.Currency,
# ISNULL((CASE WHEN a.BillType = '收款' AND a.Remarks LIKE '放款%' THEN '订单收入'
# 			 WHEN a.BillType = '分账' AND a.Remarks LIKE '平台佣金扣佣%' THEN '订单收入'
# 			 WHEN a.BillType = '扣款' AND a.Remarks LIKE '平台佣金扣佣%' THEN '订单收入'
# 			 WHEN a.BillType = '收款'
# 				 AND a.Remarks NOT LIKE '放款%'
# 				 AND a.Remarks NOT LIKE '赔付%'
# 				 AND a.Remarks NOT LIKE 'DDP关税%'
# 				 AND REPLACE(a.Remarks, ' ', '_') NOT LIKE '菜鸟代发_代发%'
# 				 AND a.Remarks NOT LIKE '违背发货%'
# 				 AND a.Remarks NOT LIKE '本地退买家%'
# 				 AND REPLACE(a.Remarks, ' ', '_') NOT LIKE '菜鸟物流费代扣_代扣%'
# 				 AND a.Remarks NOT LIKE '巴西%'
# 				 AND a.Remarks NOT LIKE '退还EPR费用%'
# 				 AND a.Remarks NOT LIKE '环保费%'
# 				 AND a.Remarks NOT LIKE 'AE_EU_ENVIRONMENT%'
# 				 AND a.Remarks NOT LIKE '佣金退回%'
# 				 AND a.Remarks NOT LIKE '平台佣金退回%'
# 				 AND a.Remarks NOT LIKE '联盟佣金退回%'
# 				 AND REPLACE(a.Remarks, ' ', '_') NOT LIKE '3PL_%'
# 				 AND REPLACE(a.Remarks, ' ', '_') NOT LIKE '4PL_%'
# 			     AND a.Remarks NOT LIKE '退款垫付%'
# 				 AND a.Remarks NOT LIKE '半托管%'
# 			 THEN '订单收入'
# 			 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE 'For_quality_or%' THEN '订单退款'
# 			 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_trade_order_refund%' THEN '订单退款'
# 			 WHEN a.BillType = '退款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_trade_order_refund%' THEN '订单退款'
# 			 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE 'trade_order%' THEN '订单退款'
# 			 WHEN a.BillType = '退款' AND REPLACE(a.Remarks, ' ', '_') LIKE 'trade_order%' THEN '订单退款'
# 			 WHEN a.BillType = '扣款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_For_quality_or_delivery_issue%' THEN '订单退款'
# 			 WHEN a.BillType = '退款' AND REPLACE(a.Remarks, ' ', '_') LIKE '退款_For_quality_or_delivery_issue%' THEN '订单退款'
# 			 WHEN a.BillType = '收款' AND a.Remarks LIKE '佣金退回%' THEN '订单退款'
# 			 WHEN a.BillType = '扣款' AND a.Remarks LIKE '佣金退回%' THEN '订单退款'
# 			 WHEN a.BillType = '分账退款' AND a.Remarks LIKE '佣金退回%' THEN '订单退款'
# 			 WHEN a.BillType = '收款' AND a.Remarks LIKE '平台佣金退回%' THEN '订单退款'
# 			 WHEN a.BillType = '扣款' AND a.Remarks LIKE '平台佣金退回%' THEN '订单退款'
# 			 WHEN a.BillType = '分账退款' AND a.Remarks LIKE '平台佣金退回%' THEN '订单退款'
# 			 WHEN a.BillType = '退款'
# 				 AND a.Remarks NOT LIKE '退款%'
# 				 AND a.Remarks NOT LIKE 'DDP关税%'
# 				 AND a.Remarks NOT LIKE '违背发货%'
# 				 AND a.Remarks NOT LIKE '本地退买家%'
# 				 AND REPLACE(a.Remarks, ' ', '_') NOT LIKE '菜鸟物流费代扣_代扣%'
# 				 AND a.Remarks NOT LIKE '巴西%'
# 				 AND a.Remarks NOT LIKE '退还EPR费用%'
# 				 AND REPLACE(a.Remarks, ' ', '_') NOT LIKE 'trade_order%'
# 			 THEN '订单退款'
# 	  ELSE '非订单' END),'')
#
# UNION ALL
#
# SELECT '俄罗斯' AS '速卖通区域',MAX(a.BillDate) '结算时间',a.BusinessOrder '平台订单号',a.OrderSourceID '店铺ID',a.OrderSourceName '店铺',a.Currency '币种',
# ISNULL((
#  CASE WHEN a.BillType = '放款' THEN '订单收入'
# 	  WHEN a.BillType = '退款' THEN '订单退款'
# 	  WHEN a.BillType = '分账' AND a.TranType LIKE '%平台佣金' THEN '订单收入'
# 	  WHEN a.BillType = '分账退款' AND a.TranType LIKE '%平台佣金' THEN '订单退款'
# 	  WHEN a.BillType = '垫付追偿' THEN '订单退款'
# 	  ELSE '非订单' END),'') AS '单据类型',
# SUM(
#  CASE WHEN a.BillType = '放款' THEN ISNULL(a.AmountOfMoney,0)
# 	  WHEN a.BillType = '退款' THEN ISNULL(a.AmountOfMoney,0)
# 	  WHEN a.BillType = '垫付追偿' THEN ISNULL(a.AmountOfMoney,0)
# ELSE 0 END) AS '销售收入',
# SUM(
#   CASE WHEN a.BillType = '分账' AND a.TranType LIKE '%平台佣金' THEN ISNULL(a.AmountOfMoney,0)
# 	   WHEN a.BillType = '分账退款' AND a.TranType LIKE '%平台佣金' THEN ISNULL(a.AmountOfMoney,0)
# ELSE 0 END) AS '佣金',
# SUM(
#   CASE WHEN a.BillType = '垫资追缴' AND a.TranType = '垫资追缴' THEN ISNULL(a.AmountOfMoney,0)
# 	   WHEN a.BillType = '垫付' AND a.TranType = '退款物流垫资' THEN ISNULL(a.AmountOfMoney,0)
# 	   WHEN a.BillType = '垫付' AND a.TranType = '退款使用垫资' THEN ISNULL(a.AmountOfMoney,0)
# 	   WHEN a.BillType = '拒付追缴' THEN ISNULL(a.AmountOfMoney,0)
# 	   WHEN a.BillType = '收款' AND a.TranType = '违背发货承诺订单扣罚' THEN ISNULL(a.AmountOfMoney,0)
# 	   WHEN a.BillType = '扣款' AND a.TranType = '违背发货承诺订单扣罚' THEN ISNULL(a.AmountOfMoney,0)
# 	   WHEN a.BillType = '分账' AND a.TranType NOT LIKE '%平台佣金' AND a.TranType NOT LIKE '%联盟佣金' THEN ISNULL(a.AmountOfMoney,0)
# 	   WHEN a.BillType = '分账退款' AND a.TranType NOT LIKE '%平台佣金' AND a.TranType NOT LIKE '%联盟佣金' THEN ISNULL(a.AmountOfMoney,0)
# 	   WHEN a.BillType = '垫资追缴' AND a.TranType NOT IN ('垫资追缴') THEN ISNULL(a.AmountOfMoney,0)
# 	   WHEN a.BillType = '垫付' AND a.TranType NOT IN ('退款物流垫资','退款使用垫资') THEN ISNULL(a.AmountOfMoney,0)
# 	   WHEN a.BillType = '收款' AND a.TranType NOT IN ('违背发货承诺订单扣罚') THEN ISNULL(a.AmountOfMoney,0)
# 	   WHEN a.BillType = '扣款' AND a.TranType NOT IN ('违背发货承诺订单扣罚') THEN ISNULL(a.AmountOfMoney,0)
# 	   WHEN a.BillType = '换汇结算' AND a.TranType NOT IN ('AEP4P回款金充值') THEN ISNULL(a.AmountOfMoney,0)
# ELSE 0 END) AS '平台其他费用',
# 0 AS '平台运费',
# SUM(CASE WHEN a.BillType = '换汇结算' AND a.TranType = 'AEP4P回款金充值' THEN ISNULL(a.AmountOfMoney,0)
# ELSE 0 END) AS '充值广告费',
# SUM(CASE WHEN a.BillType = '分账' AND a.TranType LIKE '%联盟佣金' THEN ISNULL(a.AmountOfMoney,0)
# 		 WHEN a.BillType = '分账退款' AND a.TranType LIKE '%联盟佣金' THEN ISNULL(a.AmountOfMoney,0)
# ELSE 0 END) AS '广告费',
# 0 AS '财务费用'
# FROM RB_Crawler_Aliexpress_RussianSettlement a WITH(NOLOCK)
# WHERE ((a.BillType !='汇兑' AND a.BillType !='换汇结算') OR (a.BillType = '换汇结算' AND a.TranType = 'AEP4P回款金充值'))
# AND a.BillDate >= @startdate
# AND a.BillDate <= @enddate
# --AND a.BillDate >= '2023-12-01'
# --AND a.BillDate < '2024-01-01'
# --AND a.BusinessOrder='5270530352679321'
# --AND a.OrderSourceID = 6160
# -- AND {{ a.OrderSourceID IN (@OrderSourceID) }}
# GROUP BY a.BusinessOrder,a.OrderSourceID,a.OrderSourceName,a.Currency,
# ISNULL((
#  CASE WHEN a.BillType = '放款' THEN '订单收入'
# 	  WHEN a.BillType = '退款' THEN '订单退款'
# 	  WHEN a.BillType = '分账' AND a.TranType LIKE '%平台佣金' THEN '订单收入'
# 	  WHEN a.BillType = '分账退款' AND a.TranType LIKE '%平台佣金' THEN '订单退款'
# 	  WHEN a.BillType = '垫付追偿' THEN '订单退款'
# 	  ELSE '非订单' END),'')
#         '''
#     query_collect_交易明细_detail_mod5id = '''
#         -- 俄罗斯的应收明细
#     SELECT
#     	'俄罗斯' AS '速卖通区域',
#     	OrderSourceID as '店铺ID',
#       BusinessOrder as '平台订单号',
#      	Currency AS '币种',
#     	'订单收入' AS '单据类型',
#     	ROUND(SUM (
#     		(
#     		CASE
#
#     				WHEN TranType IN ( '交易物流费','交易商品','交易货款') THEN
#     				ISNULL( CAST ( replace( AmountOfMoney, ',', '' ) AS FLOAT ), 0 )
#     			else 0 END
#     			)
#     		),2) AS '销售收入',
#     		ROUND(SUM (
#     			(
#     			CASE
#
#     					WHEN TranType IN ( '交易物流平台佣金', '交易货款平台佣金' ) THEN
#     					ISNULL( CAST ( replace( AmountOfMoney, ',', '' ) AS FLOAT ), 0 )
#     				else 0 END
#     				)
#     			),2) AS '佣金',
#     			0 AS '平台其他费用',
#     				0 as '平台运费',
#     				0 as '充值广告费',
#     				0 as '广告费',
#     				0 as '财务费用'
#     			from [AliExpressPlatformPRD].[dbo].[AliexpressRussianSettlement_20240201] (nolock)
#                 where id in
#     			(select MIN(Id)
#     			FROM
#     				[AliExpressPlatformPRD].[dbo].[AliexpressRussianSettlement_20240201] (nolock)
#     			WHERE
#     				__CrawlConfigId = '499193505267781'
#     				AND TIME >= '2024-01-01 00:00:00'
#     				AND TIME <= '2024-01-31 23:59:59'
#     				AND type IN ( '分账', '放款' )
# 						-- and OrderSourceID in ('20481','12027')
#     			GROUP BY RowMd5)
#     			GROUP BY
#     				OrderSourceID,BusinessOrder,Currency
#     				UNION ALL
#     			SELECT
#     				'俄罗斯' AS '速卖通区域',
#     				OrderSourceID as '店铺ID',
#       	BusinessOrder as '平台订单号',
#     				Currency AS '币种',
#     				'非订单' AS '单据类型',
#     				0 AS '销售收入',
#     				0 AS '佣金',
#     				ROUND(SUM (
#     					(
#     					CASE
#
#     							WHEN TranType IN ( '垫资追缴', '退款物流垫资', '退款使用垫资','交易货款','交易物流费','违背发货承诺订单扣罚') THEN
#     							ISNULL( CAST ( replace( AmountOfMoney, ',', '' ) AS FLOAT ), 0 )
#     						else 0 END
#     						)
#     					),2) AS '平台其他费用' ,
#     				0 as '平台运费',
#     				ROUND(SUM (
#     					(
#     					CASE
#
#     							WHEN Remarks  like 'AEP4P回款金充值%' THEN
#     							ISNULL( CAST ( replace( AmountOfMoney, ',', '' ) AS FLOAT ), 0 )
#     						else 0 END
#     						)
#     					),2) as '充值广告费',
#     				ROUND(SUM (
#     				(
#     				CASE
#
#     						WHEN TranType IN ( '交易货款联盟佣金' ) THEN
#     						ISNULL( CAST ( replace( AmountOfMoney, ',', '' ) AS FLOAT ), 0 )
#     					else 0 END
#     					)
#     				),2) as '广告费',
#     				0 as '财务费用'
#     			from [AliExpressPlatformPRD].[dbo].[AliexpressRussianSettlement_20240201] (nolock)
#                 where id in
#     			(select MIN(Id)
#     			FROM
#     				[AliExpressPlatformPRD].[dbo].[AliexpressRussianSettlement_20240201] (nolock)
#     			WHERE
#     					__CrawlConfigId = '499193505267781'
#     					AND TIME >= '2024-01-01 00:00:00'
#     					AND TIME <= '2024-01-31 23:59:59'
#     					AND type IN ( '垫资追缴', '垫付','分账','分账退款' ,'换汇结算','拒付追缴','扣款')
# 							-- and OrderSourceID in ('20481','12027')
#     			GROUP BY RowMd5)
#     			GROUP BY
#     				OrderSourceID,BusinessOrder,Currency
#     UNION ALL
#     				SELECT
#     					'俄罗斯' AS '速卖通区域',
#     					OrderSourceID as '店铺ID',
#              BusinessOrder as '平台订单号',
#     				 	Currency AS '币种',
#     					'订单退款' AS '单据类型',
#     					ROUND(SUM (
#     						(
#     						CASE
#
#     								WHEN TranType IN ( '交易物流费','交易商品','交易货款' ) THEN
#     								ISNULL( CAST ( replace( AmountOfMoney, ',', '' ) AS FLOAT ), 0 )
#     							else 0 END
#     							)
#     						),2) AS '销售收入',
#     						ROUND(SUM (
#     							(
#     							CASE
#
#     									WHEN TranType IN ( '交易物流平台佣金', '交易货款平台佣金' ) THEN
#     									ISNULL( CAST ( replace( AmountOfMoney, ',', '' ) AS FLOAT ), 0 )
#     								else 0 END
#     								)
#     							),2) AS '佣金',
#     							0 AS '平台其他费用' ,
#     												0 as '平台运费',
#     				      0 as '充值广告费',
#     			      	0 as '广告费',
#     					0 as '财务费用'
#     			from [AliExpressPlatformPRD].[dbo].[AliexpressRussianSettlement_20240201] (nolock)
#                 where id in
#     			(select MIN(Id)
#     			FROM
#     				[AliExpressPlatformPRD].[dbo].[AliexpressRussianSettlement_20240201] (nolock)
#     			WHERE
#                      __CrawlConfigId = '499193505267781'
#     				AND TIME >= '2024-01-01 00:00:00'
#     				AND TIME <= '2024-01-31 23:59:59'
#     				AND type IN ( '分账退款', '退款', '垫付追偿' )
# 						-- and OrderSourceID in ('20481','12027')
#     			GROUP BY RowMd5)
#     			GROUP BY
#     				OrderSourceID,BusinessOrder,Currency
#     	 union all
#     	 -- 国际支付宝的应收明细
#     SELECT '国际支付宝' AS '速卖通区域',  OrderSourceID as '店铺ID',
#     	merchant_order_number	  as '平台订单号',currency AS '币种','订单收入' AS '单据类型',
#      ROUND(SUM (
#     						(
#     						CASE
#
#     								WHEN remarks like '放款%' THEN
#     								ISNULL(  CAST (  amount AS FLOAT ) , 0 )
#     							else 0 END
#     							)
#     						),2) AS '销售收入',
#     	 ROUND(SUM (
#     						(
#     						CASE
#
#     								WHEN remarks like '平台佣金扣佣%' THEN
#     								ISNULL(  CAST (  amount AS FLOAT ) , 0 )
#     							else 0 END
#     							)
#     						),2) AS '佣金',
#     			     0 AS '平台其他费用',
#     								0 AS '平台运费',
#     						0 as '充值广告费',
#     						0 AS '广告费',
#     						0 as '财务费用'
#                 FROM [AliExpressPlatformPRD].[dbo].[BillHistory_20240201] ( nolock )
#                 where id in
#     			(select MIN(Id)
#     			FROM
#     		      [AliExpressPlatformPRD].[dbo].[BillHistory_20240201] ( nolock )
#     			WHERE
#                          bill_date >= '2024-01-01 00:00:00'
#     					AND bill_date <= '2024-01-31 23:59:59'
#     					AND __CrawlConfigId = '499193504866373'
#     					AND bill_type in ('收款','扣款','分账')
# 							-- and OrderSourceID in ('20481','12027')
#     			GROUP BY RowMd5)
#                 GROUP BY
#     			OrderSourceID,merchant_order_number,currency
#     	UNION ALL
#
#     SELECT '国际支付宝' AS '速卖通区域', OrderSourceID as '店铺ID',
#     		merchant_order_number	  as '平台订单号', currency AS '币种','订单退款' AS '单据类型',
#      ROUND(SUM (
#     						(
#     						CASE
#
#     								WHEN  remarks like '退款	trade order refund%'
#     								or remarks like '退款	For quality or delivery issue%'
#     								or remarks like 'For quality or delivery issue%'
#     								or remarks like 'For quality or deliv%'
#     								or remarks like 'trade order%'  THEN
#     								ISNULL(  CAST (  amount AS FLOAT ) , 0 )
#     							else 0 END
#     							)
#     						),2) AS '销售收入',
#     	 ROUND(SUM (
#     						(
#     						CASE
#
#     								WHEN remarks like '佣金退回%' or remarks like '平台佣金退回%'  THEN
#     								ISNULL(  CAST (  amount AS FLOAT ) , 0 )
#     							else 0 END
#     							)
#     						),2) AS '佣金',
#     			      0 AS '平台其他费用',
#     						0	AS '平台运费',
#     						0 as '充值广告费',
#     						0 AS '广告费',
#     						0 as '财务费用'
#       FROM [AliExpressPlatformPRD].[dbo].[BillHistory_20240201] ( nolock )
#                 where id in
#     			(select MIN(Id)
#     			FROM
#     		      [AliExpressPlatformPRD].[dbo].[BillHistory_20240201] ( nolock )
#     			 WHERE  bill_date >= '2024-01-01 00:00:00'
#     			AND bill_date <= '2024-01-31 23:59:59'
#     			AND __CrawlConfigId = '499193504866373'
#     			AND bill_type in ('扣款','分账退款','退款','收款')
# 					-- and OrderSourceID in ('20481','12027')
#     			GROUP BY RowMd5)
#                 GROUP BY
#     			OrderSourceID,merchant_order_number,currency
#                union all
#     SELECT '国际支付宝' AS '速卖通区域', OrderSourceID as '店铺ID',
#    (case when merchant_order_number!='' and LEN(merchant_order_number)<=20  then merchant_order_number
# 		 when merchant_order_number='' and LogisticsOrderNo!='AE线上发货' and LEN(LogisticsOrderNo)<=20 then LogisticsOrderNo
# 		 else '' end)   as '平台订单号', currency AS '币种','非订单' AS '单据类型',
#     0  AS '销售收入',
#     	0 AS '佣金',
#     			 ROUND(SUM (
#     						(
#     						CASE
#     								WHEN remarks like '菜鸟代发	代发_服务赔付%'
#     								or remarks like '菜鸟代发	代发_退出口目的国关税%'
#     								or remarks like '菜鸟代发	代发_货值赔付%'
#     								or remarks like '菜鸟代发	代发_配送附加费%'
#     								or remarks like 'DDP关税结算%'
#     								or remarks like 'DDP关税退款%'
#     								or remarks like '退还EPR费用%'
#     								or remarks like '菜鸟物流费代扣	代扣_出口目的国关税%'
#     								or remarks like '菜鸟物流费代扣	代扣_目的国进口税金%'
#     								or remarks like '菜鸟物流费代扣	代扣_冲货处理附加费%'
#     								or remarks like '菜鸟物流费代扣	代扣_配送附加费%'
#     								or remarks like '菜鸟物流费代扣	代扣_超时发货费%'
#     								or remarks like '菜鸟物流费代扣	代扣_退货服务费%'
#     								or remarks like '菜鸟物流费代扣	代扣_退货仓内处理费%'
#     								or remarks like '菜鸟物流费代扣	代扣_逆向退货配送费%'
#     								or remarks like '买家退货运费%'
#     								or remarks like 'AE_EU_ENVIRONMENT%'
#     								or remarks like '限时达不达标处罚%'
#     								or remarks like '4PL_PLATFORM_RECOVER%'
#     								or remarks like 'EPR费用%'
#     								or remarks like 'POP_SELLER_DELIVER_PUNISH_%'
#     								or remarks like  '违背发货承诺订单扣罚%'
#     								or remarks like '菜鸟代发	代发_美容健康%'
#     								or  remarks like '赔付%'
#     								or remarks like '垫资还款冻结%'
#     					    	or remarks like '退款垫付%'
#     								or remarks like '环保费%'
#     								or remarks like '中东地区满包邮活动服务费%'
#     								or remarks like '垫资/欠费还款的解冻并扣款%'
#                                     or remarks like '菜鸟物流费代扣	代扣_代付税金%'
#     								or remarks like '菜鸟物流费代扣	代扣_代付巴西%'
#     								or remarks like '巴西%'
#     								or remarks like '菜鸟物流费代扣	代扣_消退入库操作费%'
#     								or remarks like '本地退买家退货%'
#     								or remarks like '菜鸟物流费代扣	代扣_代付巴西ICMS税%'
#     								or remarks like '本地退退货运费%'
# 								    or remarks like '本地退退货质检费%'
#     								or  remarks like '3PL_REFUND_COMPENSATE_ITEM%'
#     								or remarks like '扣款	扣款用于偿还垫资欠款%'
#     								or remarks like '半托管JIT超时处罚%'
#                                     or remarks like '半托管买家退货代扣退货质检费%'
#                                     or remarks like '半托管买家退货代扣运费%'
#                                     or  remarks like '本地退退货操作费%'  THEN
#     								ISNULL(  CAST (  amount AS FLOAT ) , 0 )
#     							else 0 END
#     							)
#     						),2) AS '平台其他费用',
#     						ROUND(SUM (
#     						(
#     						CASE
#     								WHEN remarks like '菜鸟物流费代扣	代扣_正向配送费%'  or remarks like '菜鸟代发	代发_正向配送费%' or remarks like '半托管发货物流费%'  THEN
#     								ISNULL(  CAST (  amount AS FLOAT ) , 0 )
#     							else 0 END
#     							)
#     						),2) AS '平台运费',
#     						 ROUND(SUM (
#     						(
#     						CASE
#     								WHEN remarks like '直通车充值%'  or remarks like 'AEP4P回款金充值%'  THEN
#     								ISNULL(  CAST (  amount AS FLOAT ) , 0 )
#     							else 0 END
#     							)
#     						),2) AS '充值广告费',
#     						ROUND(SUM (
#     						(
#     						CASE
#     								WHEN remarks like '联盟佣金扣佣%' or remarks like '联盟佣金退回%' THEN
#     								ISNULL(  CAST (  amount AS FLOAT ) , 0 )
#     							else 0 END
#     							)
#     						),2) AS '广告费',
#     						 ROUND(SUM (
#     						(
#     						CASE
#     								WHEN  TranType='服务费' or remarks like '自动转账%'  THEN
#     								ISNULL(  CAST (  amount AS FLOAT ) , 0 )
#     							else 0 END
#     							)
#     						),2)  as '财务费用'
#
#     			FROM [AliExpressPlatformPRD].[dbo].[BillHistory_20240201] ( nolock )
#                 where id in
#     			(select MIN(Id)
#     			FROM
#     		      [AliExpressPlatformPRD].[dbo].[BillHistory_20240201] ( nolock )
#     	       WHERE bill_date >= '2024-01-01 00:00:00'
#                AND bill_date <= '2024-01-31 23:59:59'
#                AND __CrawlConfigId = '499193504866373'
# 							-- and OrderSourceID in ('20481','12027')
#                AND bill_type in ('收款','退款','扣款','服务费','分账','分账退款','垫付')
#     			GROUP BY RowMd5)
#                 GROUP BY
#     			OrderSourceID, case when merchant_order_number!='' and LEN(merchant_order_number)<=20  then merchant_order_number
# 		 when merchant_order_number='' and LogisticsOrderNo!='AE线上发货' and LEN(LogisticsOrderNo)<=20 then LogisticsOrderNo
# 		 else '' end  ,currency;
#     '''
#     database={'erp测试库':'sqlserver|10.158.158.48|1433|sa|zhcx@2021#PD|irobotbox','采集':'sqlserver|116.205.173.204|1433|datareadonly|5FP%NijFUx@B&TFt|AliExpressPlatformPRD',
#               'erp正式库':'sqlserver|erpdb-readonly.zhcxkj.com|1433|irobotboxuser|HskwPanda047RAHRAxqktlref|irobotbox'}
#     datacomp=DataComparison(database)
#     value=('销售收入', '佣金', '平台其他费用', '平台运费', '充值广告费', '广告费','财务费用')
#     keys=('速卖通区域','平台订单号','店铺ID','币种','单据类型')
#     collect_data=datacomp.data_organization('采集',query_collect_交易明细_detail_mod5id,keys,value)
#     erp_data=datacomp.data_organization('erp正式库',query_erp_custom_report,keys,value)
#     datacomp.data_compare(collect_data,erp_data,value)
if __name__=='__main__':
 # 利润匹配销售收入
 profit='''
     SELECT
        Ordercode,
        ROUND( SUM ( Profits ), 2 ) AS '总利润',
        ROUND( SUM ( Incomes ), 2 ) AS '总收入',
        ROUND( SUM ( SalesPrice ), 2 ) AS '销售收入',
        ROUND( SUM ( TransportPay ), 2 ) AS '运费收入',
        ROUND( SUM ( SalesRefunds ), 2 ) AS '退款金额',
        ROUND( SUM ( PromotionRebates ), 2 ) AS '促销折扣',
        ROUND( SUM ( OtherCredits ), 2 ) AS '其他收入',
        ROUND( SUM ( CommisionFee ), 2 ) AS '交易佣金',
        ROUND( SUM ( FBATransactionFees ), 2 ) AS 'FBA操作费',
        ROUND( SUM ( OrderSourceOtherDebits ), 2 ) AS '平台其他支出',
        ROUND( SUM ( CostOfAdvertising ), 2 ) AS '广告费用',
        ROUND( SUM ( BuyPrice ), 2 ) AS '采购成本',
        ROUND( SUM ( PackageFees ), 2 ) AS '本地仓运费'
    FROM
        [irobotbox].[dbo].[RB_Report_OrderSettlement] ( NOLOCK )
    WHERE
        CustomerID = 1
    -- 	AND Ordercode = '202401291632164432404'
    and id<=623973536
    and ShipTime>='2024-05-01'
    and ShipTime<'2024-06-01'
    -- 排除亚马逊
    and OrderSourceType!=1
    GROUP BY
        Ordercode;
 '''
 sale='''
     SELECT
        Ordercode,
        ROUND( SUM ( Profits ), 2 ) AS '总利润',
        ROUND( SUM ( Incomes ), 2 ) AS '总收入',
        ROUND( SUM ( SalesPrice ), 2 ) AS '销售收入',
        ROUND( SUM ( TransportPay ), 2 ) AS '运费收入',
        ROUND( SUM ( SalesRefunds ), 2 ) AS '退款金额',
        ROUND( SUM ( PromotionRebates ), 2 ) AS '促销折扣',
        ROUND( SUM ( OtherCredits ), 2 ) AS '其他收入',
        ROUND( SUM ( CommisionFee ), 2 ) AS '交易佣金',
        ROUND( SUM ( FBATransactionFees ), 2 ) AS 'FBA操作费',
        ROUND( SUM ( OrderSourceOtherDebits ), 2 ) AS '平台其他支出',
        ROUND( SUM ( CostOfAdvertising ), 2 ) AS '广告费用',
        ROUND( SUM ( BuyPrice ), 2 ) AS '采购成本',
        ROUND( SUM ( PackageFees ), 2 ) AS '本地仓运费'
    FROM
        [irobotbox].[dbo].[RB_Finance_Settlement] ( NOLOCK )
    WHERE
        CustomerID = 1
    -- 	AND Ordercode = '202401291632164432404'
    -- and id<=548958243
    and ShipTime>='2024-05-01'
    and ShipTime<'2024-06-01'
    and OrderSourceType!=1
    GROUP BY
        Ordercode;
 '''
 database={'erp正式库':'sqlserver|erpdb-readonly.zhcxkj.com|1433|irobotboxuser|HskwPanda047RAHRAxqktlref|irobotbox'}
 datacomp=DataComparison(database)
 value=('总利润', '总收入', '销售收入', '运费收入', '退款金额', '促销折扣','其他收入','交易佣金','FBA操作费','平台其他支出','广告费用','采购成本','本地仓运费')
 keys='Ordercode'
 profit_data=datacomp.data_organization('erp正式库',profit,keys,value)
 sale_data=datacomp.data_organization('erp正式库',sale,keys,value)
 datacomp.data_compare(profit_data,sale_data,value)
 #    应付
 #    erp="""
 #    SELECT d.PackageID as '包裹号',
	#  sum(CASE WHEN t.CostType not in (9,10) THEN d.SupplierPrice  ELSE 0 END)  AS '运费',
 #   sum( CASE WHEN t.CostType  in (9,10) THEN d.SupplierPrice ELSE 0 END)  AS '税费'
	#  from RB_Logistics_Reconciliation_Detail_Package( NOLOCK ) d left join RB_Logistics_Reconciliation( NOLOCK ) t on d.ProofID=t.id
	# 		where t.CustomerID = 1
 #        -- 	AND ID IN ( 12829, 12844 )
 #            and t.ConfirmTime>='2024-03-01'
 #            and t.ConfirmTime<'2024-04-01'
 #            and t.FinanceStatus=2
 #            and t.TransportSupplierName not in ('LAZADA',
 #            'MAILATINAMERICA SA（南美物流）',
 #            'Shopee',
 #            '东莞市速奔货运代理有限公司',
 #            '上海万色速递有限公司',
 #            '深圳市安骏物流有限公司',
 #            '速卖通V2平台',
 #            '速卖通半托',
 #            '速卖通全托',
 #            '顺丰速运有限公司',
 #            '优速快递')
	# 		-- and d.PackageID='49179522'
 #            and t.CostType in (1,7,2,3,8,9,10)
	# 					group by d.PackageID;
 #    """
 #    kindee='''
 #    SELECT
 #    -- 	ReconciliationLot AS '批次号',
 #        PackageID AS '包裹号',
 #    sum(CASE WHEN ExpenseType = 1 THEN SupplierPrice ELSE 0 END)  AS '运费',
 #    sum(CASE WHEN ExpenseType = 2 THEN SupplierPrice ELSE 0 END)  AS '税费'
 #    FROM
 #        Db_Inventory_Logisticspayablecost
 #    WHERE
 #        AccountDate = '2024-03-01'
 #        group by PackageID
 #    '''
    # 暂估核对运费 和税费
# if __name__=='__main__':
    #    应付直发订单
    # erp="""
    #    SELECT d.ProofID as '批次号',
    #     d.PackageID as '包裹号',
    #  sum(CASE WHEN t.CostType=1 THEN d.SupplierPrice  ELSE 0 END)  AS '运费',
    #   sum( CASE WHEN t.CostType=9 THEN d.SupplierPrice ELSE 0 END)  AS '税费'
    #  from RB_Logistics_Reconciliation_Detail_Package( NOLOCK ) d left join RB_Logistics_Reconciliation( NOLOCK ) t on d.ProofID=t.id
    # 		where t.CustomerID = 1
    #        -- 	AND ID IN ( 12829, 12844 )
    #            and d.SystemDeliveryDate>='2024-03-01'
    #            and d.SystemDeliveryDate<'2024-04-01'
    #            and t.FinanceStatus=2
    #            and t.TransportSupplierName not in ('LAZADA',
    #            'MAILATINAMERICA SA（南美物流）',
    #            'Shopee',
    #            '东莞市速奔货运代理有限公司',
    #            '上海万色速递有限公司',
    #            '深圳市安骏物流有限公司',
    #            '速卖通V2平台',
    #            '速卖通半托',
    #            '速卖通全托',
    #            '顺丰速运有限公司',
    #            '优速快递')
    # 		-- and d.PackageID='49179522'
    #            and t.CostType in (1,9)
    # 					group by d.ProofID,d.PackageID;
    #    """
    # kindee='''
    #    SELECT
    #     	ReconciliationLot AS '批次号',
    #        PackageID AS '包裹号',
    #    sum(CASE WHEN ExpenseType = 1 THEN SupplierPrice ELSE 0 END)  AS '运费',
    #    sum(CASE WHEN ExpenseType = 2 THEN SupplierPrice ELSE 0 END)  AS '税费'
    #    FROM
    #        Db_Inventory_Logisticspayablecost
    #    WHERE
    #        AccountDate in  ('2024-03-01','2024-04-01')
    #        and
    #        BusinessType=3
    #        group by ReconciliationLot,PackageID
    #    '''
    #    应付备库订单
    # erp = """
    #            SELECT
    #           d.ProofID as '批次号',
    #           d.PackageID as '包裹号',
    #         sum(CASE WHEN t.CostType  in (2,3,8) THEN d.SupplierPrice  ELSE 0 END)  AS '运费',
    #          sum( CASE WHEN t.CostType=10 THEN d.SupplierPrice ELSE 0 END)  AS '税费'
    #         from RB_Logistics_Reconciliation_Detail_Package( NOLOCK ) d left join RB_Logistics_Reconciliation( NOLOCK ) t
	# 					on d.ProofID=t.id
    #         left join RB_Procurement_Order_Package ( NOLOCK ) p on d.PackageID=p.id
    #        		where t.CustomerID = 1
    #               -- 	AND ID IN ( 12829, 12844 )
    #                   and p.CompleteTime>='2024-03-01'
    #                   and p.CompleteTime<'2024-04-01'
    #                   and t.FinanceStatus=2
    #                   and t.TransportSupplierName not in ('LAZADA',
    #                   'MAILATINAMERICA SA（南美物流）',
    #                   'Shopee',
    #                   '东莞市速奔货运代理有限公司',
    #                   '上海万色速递有限公司',
    #                   '深圳市安骏物流有限公司',
    #                   '速卖通V2平台',
    #                   '速卖通半托',
    #                   '速卖通全托',
    #                   '顺丰速运有限公司',
    #                   '优速快递')
    #        		-- and d.PackageID='49179522'
    #                   and t.CostType in (2,3,8,10)
    #        					group by d.ProofID,d.PackageID;
    #           """
    # kindee = '''
    #           SELECT
    #           ReconciliationLot AS '批次号',
    #           PackageID AS '包裹号',
    #           sum(CASE WHEN ExpenseType = 1 THEN SupplierPrice ELSE 0 END)  AS '运费',
    #           sum(CASE WHEN ExpenseType = 2 THEN SupplierPrice ELSE 0 END)  AS '税费'
    #           FROM
    #               Db_Inventory_Logisticspayablecost
    #           WHERE
    #               AccountDate in  ('2024-03-01','2024-04-01')
    #               and BusinessType=2
    #               group by ReconciliationLot,PackageID
    #           '''
    ###预估拉直发单
    #     erp="""
    #    -- 暂估下载的数据
    #     SELECT
    #       ID as '包裹号',
    #        FinalShippingFee as '运费'
    #      -- TariffFee as '税费'
    #     -- 	IsAccount,
    #     -- 	IsTaxAccount
    #     FROM
    #         RB_Procurement_Order_Package ( NOLOCK )
    #     WHERE
    #         CustomerID = 1
    #         and ShipTime>='2024-03-01'
    #         and ShipTime<'2024-04-01'
    #         and IsAccount=0
    #         and IsStockOrderWithPackage=0
    #         and TransportSupplierID not in ('112',
    # '224',
    # '256',
    # '290',
    # '335',
    # '384',
    # '415',
    # '604',
    # '605',
    # '654',
    # '655')
    #       --  and IsTaxAccount=0
    #     """
    #     kindee='''
    #        SELECT
    #         PackageID as '包裹号',
    #        FinalShippingFee  as '运费'
    #       -- TariffFee  as '税费'
    #     FROM
    #         Db_Inventory_Logisticsestimationcost
    #     WHERE
    #     -- 	RelationNumber = '48431843'
    #         BusinessType=3
    #         and AccountDate='2024-03-01';
    #     '''
    #预估拉备库订单，按交运时间
    # erp = """
    #   -- 暂估下载的数据
    #    SELECT
    #      ID as '包裹号',
    #      FinalShippingFee as '运费'
    #     --  TariffFee as '税费'
    #    -- 	IsAccount,
    #    -- 	IsTaxAccount
    #    FROM
    #        RB_Procurement_Order_Package ( NOLOCK )
    #    WHERE
    #        CustomerID = 1
    #        and CompleteTime>='2024-03-01'
    #        and CompleteTime<'2024-04-01'
    #        and IsAccount=0
    #        and IsStockOrderWithPackage=1
    #       -- and IsTaxAccount=0
    #    """
    # kindee = '''
    #       SELECT
    #        PackageID as '包裹号',
    #      FinalShippingFee  as '运费'
    #     -- TariffFee  as '税费'
    #    FROM
    #        Db_Inventory_Logisticsestimationcost
    #    WHERE
    #    -- 	RelationNumber = '48431843'
    #        BusinessType=2
    #        and AccountDate='2024-03-01';
    #    '''
    # database={'erp正式库':'sqlserver|erpdb-readonly.zhcxkj.com|1433|irobotboxuser|HskwPanda047RAHRAxqktlref|irobotbox',
    #           '金蝶测试库':'mysql|10.158.158.28|3306|root|Zhcx?2021|irobotbox_finance_staging'}
    # datacomp=DataComparison(database)
    # # value='SupplierPrice'
    # value = ('运费','税费')
    # # value = ('运费')
    # keys=('批次号','包裹号')
    # erp_data=datacomp.data_organization('erp正式库',erp,keys,value)
    # kingdee_data=datacomp.data_organization('金蝶测试库',kindee,keys,value)
    # datacomp.data_compare(erp_data,kingdee_data,value)
# if __name__=='__main__':
    #索赔同步数据
    # erp='''
    #     SELECT
    #         SellerId,
    #         shipmentId,
    #         merchantSKU,
    #         fnsku,
    #         ExpectedQuantityDetail,
    #         TotalLocatedQuantity,
    #         TotalReceivedQuantity,
    #         TotalDiscrepancyQuantity
    #     FROM
    #         RB_Amazon_Claim_Inbound_Crawl ( NOLOCK )
    #     WHERE
    #         CustomerID = 1
    #         AND createdDate >= '2024-01-01'
    #         AND createdDate < '2024-02-01';
    # '''
    # collect='''
    #    SELECT
    #     b.SellerId as SellerId,
    #     b.shipmentId as shipmentId,
    #     a.merchantSKU as merchantSKU,
    #     a.fnsku as fnsku,
    #     cast(expectedQuantityDetail as int) as ExpectedQuantityDetail,
    #      cast(totalLocatedQuantity as int) as  TotalLocatedQuantity,
    #     cast(totalReceivedQuantity as int)  as TotalReceivedQuantity,
    #     cast(totalDiscrepancyQuantity as int)  as TotalDiscrepancyQuantity
    #     FROM
    #         AmzFbaInboundShipment ( NOLOCK ) b
    #         LEFT JOIN AmzFbaInboundShipmentDetail ( NOLOCK ) a ON a.FbaInboundShipmentId= b.id
    #     WHERE
    #         b.id IN ( SELECT MAX ( id )
    #         FROM AmzFbaInboundShipment ( NOLOCK )
    #         WHERE createdDate >= '2024-01-01' AND createdDate < '2024-02-01'
    #          GROUP BY SellerId,MarketplaceId,shipmentId )
    #          and a.FbaInboundShipmentId is not null;
    # '''
    # database={'erp正式库':'sqlserver|erpdb-readonly.zhcxkj.com|1433|irobotboxuser|HskwPanda047RAHRAxqktlref|irobotbox',
    #           '采集':'sqlserver|116.205.173.204|1433|datareadonly|5FP%NijFUx@B&TFt|AmzRobotServer',
    #           'erp测试库':'sqlserver|10.158.158.48|1433|sa|zhcx@2021#PD|irobotbox',}
    # datacomp=DataComparison(database)
    # value=('ExpectedQuantityDetail',
    # 'TotalLocatedQuantity',
    # 'TotalReceivedQuantity',
    # 'TotalDiscrepancyQuantity')
    # keys=( 'SellerId',
    # 'shipmentId',
    # 'merchantSKU',
    # 'fnsku')
    # erp_data=datacomp.data_organization('erp正式库',erp,keys,value)
    # collect_data=datacomp.data_organization('采集',collect,keys,value)
    # datacomp.data_compare(collect_data,erp_data,value)




